﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class ConsumableService
    {
        //新增一条数据
        public static bool Add(Model.Consumable model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into Consumable(");
			strSql.Append("name,normal,bad,stock,location,remarks)");
			strSql.Append(" values (");
			strSql.Append("@name,@normal,@bad,@stock,@location,@remarks)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@name", SqlDbType.VarChar,100),
					new SqlParameter("@normal", SqlDbType.Int,4),
					new SqlParameter("@bad", SqlDbType.Int,4),
					new SqlParameter("@stock", SqlDbType.Int,4),
					new SqlParameter("@location", SqlDbType.Int,4),
					new SqlParameter("@remarks", SqlDbType.VarChar,1000)};
			parameters[0].Value = model.name;
			parameters[1].Value = model.normal;
			parameters[2].Value = model.bad;
			parameters[3].Value = model.stock;
			parameters[4].Value = model.location;
			parameters[5].Value = model.remarks;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return false;
			}
			else
			{
				return true;
			}
		}

        //根据ID删除一条数据
        public static void DeleteByID(int id)
        {
            string sql = string.Format("DELETE FROM Consumable WHERE id = {0}",id);
            SQLHelper.ExecuteNonQuery(SQLHelper.sqlConnectionString,CommandType.Text,sql);
        }

        //更新一条数据
        public static bool Update(Model.Consumable model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Consumable set ");
            strSql.Append("name=@name,");
            strSql.Append("normal=@normal,");
            strSql.Append("bad=@bad,");
            strSql.Append("stock=@stock,");
            strSql.Append("location=@location,");
            strSql.Append("remarks=@remarks");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@name", SqlDbType.VarChar,100),
					new SqlParameter("@normal", SqlDbType.Int,4),
					new SqlParameter("@bad", SqlDbType.Int,4),
					new SqlParameter("@stock", SqlDbType.Int,4),
					new SqlParameter("@location", SqlDbType.Int,4),
					new SqlParameter("@remarks", SqlDbType.VarChar,1000),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.name;
            parameters[1].Value = model.normal;
            parameters[2].Value = model.bad;
            parameters[3].Value = model.stock;
            parameters[4].Value = model.location;
            parameters[5].Value = model.remarks;
            parameters[6].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据条件获取数据列表
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("select Consumable.id,Consumable.name,Consumable.normal,Consumable.bad,Consumable.stock,Consumable.remarks,Parameter.value ");
            strSql.Append(" FROM Consumable,Parameter WHERE Consumable.location =  Parameter.id ");
            if (strWhere != "")
            {
                strSql.Append(" AND "+strWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        //根据ID获取一条数据
        public static Model.Consumable SelectOneByID(int id)
        {
            string sql = string.Format("SELECT * FROM Consumable WHERE id = {0}",id);
            SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.sqlConnectionString, CommandType.Text, sql);
            Model.Consumable m = new Model.Consumable();
            if (dr.HasRows)
            {
                dr.Read();
                m.id = int.Parse(dr[0].ToString());
                m.name = dr[1].ToString();
                m.normal = int.Parse(dr[2].ToString());
                m.bad = int.Parse(dr[3].ToString());
                m.stock = int.Parse(dr[4].ToString());
                m.location = int.Parse(dr[5].ToString());
                m.remarks = dr[6].ToString();
                m.locationValue = ParameterService.SelectOne(m.location);
            }
            dr.Close();
            return m;
        }
    }
}
